package cn.iocoder.yudao.module.system.dal.mysql.gugu;

import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.module.system.dal.dataobject.gugu.ScoreSegmentDO;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.util.StringUtils;

import java.util.List;

/**
 * 高考一分一段数据 Mapper
 */
@Mapper
public interface ScoreSegmentMapper extends BaseMapperX<ScoreSegmentDO> {

    /**
     * 根据年份、省份和科目选择类型查询一分一段数据
     *
     * @param year 年份
     * @param provinceName 省份名称
     * @param subjectSelection 科目选择类型
     * @return 一分一段数据列表
     */
    default List<ScoreSegmentDO> selectByYearAndProvinceAndSubject(String year, String provinceName, String subjectSelection) {
        return selectList(new LambdaQueryWrapperX<ScoreSegmentDO>()
                .eq(ScoreSegmentDO::getYear, year)
                .eq(ScoreSegmentDO::getProvinceName, provinceName)
                .eq(ScoreSegmentDO::getSubjectSelection, subjectSelection)
                .orderByDesc(ScoreSegmentDO::getExaminationScore));
    }

    /**
     * 根据年份、省份、科目选择类型和分数查询一分一段数据
     *
     * @param year 年份
     * @param provinceName 省份名称
     * @param subjectSelection 科目选择类型
     * @param score 分数
     * @return 一分一段数据
     */
    default ScoreSegmentDO selectByYearAndProvinceAndSubjectAndScore(String year, String provinceName, String subjectSelection, String score) {
        return selectOne(new LambdaQueryWrapperX<ScoreSegmentDO>()
                .eq(ScoreSegmentDO::getYear, year)
                .eq(ScoreSegmentDO::getProvinceName, provinceName)
                .eq(ScoreSegmentDO::getSubjectSelection, subjectSelection)
                .eq(ScoreSegmentDO::getExaminationScore, score));
    }

    /**
     * 根据年份、省份、科目选择类型和具体分数查询一分一段数据（包括区间分数）
     *
     * @param year 年份
     * @param provinceName 省份名称
     * @param subjectSelection 科目选择类型
     * @param exactScore 具体分数
     * @return 一分一段数据
     */
    default ScoreSegmentDO selectByYearAndProvinceAndSubjectAndExactScore(String year, String provinceName, String subjectSelection, Integer exactScore) {
        // 首先尝试精确匹配
        ScoreSegmentDO result = selectOne(new LambdaQueryWrapperX<ScoreSegmentDO>()
                .eq(ScoreSegmentDO::getYear, year)
                .eq(ScoreSegmentDO::getProvinceName, provinceName)
                .eq(ScoreSegmentDO::getSubjectSelection, subjectSelection)
                .eq(ScoreSegmentDO::getExaminationScore, String.valueOf(exactScore)));

        if (result != null) {
            return result;
        }

        // 如果精确匹配失败，尝试查找区间
        return selectOne(new LambdaQueryWrapperX<ScoreSegmentDO>()
                .eq(ScoreSegmentDO::getYear, year)
                .eq(ScoreSegmentDO::getProvinceName, provinceName)
                .eq(ScoreSegmentDO::getSubjectSelection, subjectSelection)
                .apply("examination_score LIKE '%-%' AND CAST(SUBSTRING_INDEX(examination_score, '-', 1) AS SIGNED) <= {0} AND CAST(SUBSTRING_INDEX(examination_score, '-', -1) AS SIGNED) >= {0}", exactScore)
                .orderByDesc(ScoreSegmentDO::getExaminationScore)
                .last("LIMIT 1"));
    }

    /**
     * 检查指定条件的数据是否已存在
     *
     * @param year 年份
     * @param provinceName 省份名称
     * @param subjectSelection 科目选择类型
     * @return 是否存在
     */
    default boolean existsByYearAndProvinceAndSubject(String year, String provinceName, String subjectSelection) {
        return selectCount(new LambdaQueryWrapperX<ScoreSegmentDO>()
                .eq(ScoreSegmentDO::getYear, year)
                .eq(ScoreSegmentDO::getProvinceName, provinceName)
                .eq(ScoreSegmentDO::getSubjectSelection, subjectSelection)) > 0;
    }

    /**
     * 检查指定条件和分数的数据是否已存在
     *
     * @param year 年份
     * @param provinceName 省份名称
     * @param subjectSelection 科目选择类型
     * @param examinationScore 高考分数
     * @return 是否存在
     */
    default boolean existsByYearAndProvinceAndSubjectAndScore(String year, String provinceName, String subjectSelection, String examinationScore) {
        return selectCount(new LambdaQueryWrapperX<ScoreSegmentDO>()
                .eq(ScoreSegmentDO::getYear, year)
                .eq(ScoreSegmentDO::getProvinceName, provinceName)
                .eq(ScoreSegmentDO::getSubjectSelection, subjectSelection)
                .eq(ScoreSegmentDO::getExaminationScore, examinationScore)) > 0;
    }

    /**
     * 根据年份、省份、科目选择类型和条件查询一分一段数据
     *
     * @param year 年份
     * @param provinceName 省份名称
     * @param subjectSelection 科目选择类型
     * @param minScore 最小分数
     * @param maxScore 最大分数
     * @param batchName 批次名称
     * @return 一分一段数据列表
     */
    default List<ScoreSegmentDO> selectByYearAndProvinceAndSubjectAndCondition(String year, String provinceName,
                                                                             String subjectSelection, String minScore,
                                                                             String maxScore, String batchName) {
        LambdaQueryWrapperX<ScoreSegmentDO> wrapper = new LambdaQueryWrapperX<ScoreSegmentDO>()
                .eq(ScoreSegmentDO::getYear, year)
                .eq(ScoreSegmentDO::getProvinceName, provinceName)
                .eq(ScoreSegmentDO::getSubjectSelection, subjectSelection);

        // 处理分数范围条件
        if (StringUtils.hasText(minScore)) {
            // 对于分数区间，需要特殊处理
            wrapper.apply("CAST(SUBSTRING_INDEX(examination_score, '-', 1) AS SIGNED) >= {0}", minScore);
        }
        if (StringUtils.hasText(maxScore)) {
            // 如果是区间，取区间的最大值；如果是单一分数，直接比较
            wrapper.apply("CASE WHEN examination_score LIKE '%-%' THEN CAST(SUBSTRING_INDEX(examination_score, '-', -1) AS SIGNED) ELSE CAST(examination_score AS SIGNED) END <= {0}", maxScore);
        }

        // 处理批次名称条件
        if (StringUtils.hasText(batchName)) {
            wrapper.like(ScoreSegmentDO::getAdmissionBatchName, batchName);
        }

        return selectList(wrapper.orderByDesc(ScoreSegmentDO::getExaminationScore));
    }

    /**
     * 根据省份、年份和考试类型查询一分一段数据
     *
     * @param province 省份
     * @param year 年份
     * @param examType 考试类型
     * @return 一分一段数据列表
     */
    default List<ScoreSegmentDO> selectByProvinceYearAndType(String province, String year, String examType) {
        return selectList(new LambdaQueryWrapperX<ScoreSegmentDO>()
                .eq(ScoreSegmentDO::getProvinceName, province)
                .eq(ScoreSegmentDO::getYear, year)
                .eq(ScoreSegmentDO::getSubjectSelection, examType)
                .orderByDesc(ScoreSegmentDO::getExaminationScore));
    }
}
